/*******************************************************************************
 * Copyright ©  2011 Hulles Industries LLC <hulles.com>
 * All rights reserved
 *
 * This program is free software: you can redistribute it and/or modify
 * it under the terms of the GNU General Public License as published by
 * the Free Software Foundation, either version 3 of the License, or
 * (at your option) any later version.
 *  
 * This program is distributed in the hope that it will be useful,
 * but WITHOUT ANY WARRANTY; without even the implied warranty of
 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
 * GNU General Public License for more details.
 *
 * You should have received a copy of the GNU General Public License
 * along with this program.  If not, see <http://www.gnu.org/licenses/>.
 *     
 *******************************************************************************/
package com.hulles.sancho.engine;

import java.sql.Connection;
import java.sql.Date;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Time;
import java.sql.Timestamp;
import java.util.logging.Level;
import javax.sql.DataSource;
import com.hulles.sancho.engine.SanchoApplication.NoDataSourceException;

/**
 * DBManager is the interface between Sancho database access and the java.sql MySQL driver
 * NOTE:
 * ALL DATABASE ENTITIES (i.e. java classes created from the database) SHOULD
 * BE CONSIDERED EPHEMERAL - NO LISTENERS, NO RETENTION, NO NOTHING.
 * ASSUME THAT SOMEONE CHANGED THE UNDERLYING DB RECORD WHILE YOU HAVE IT.
 * THE ONLY DATA THAT A DATABASE ENTITY RETAINS IS THE ID: ALL OTHER DATA IS FETCHED
 * WHEN NEEDED.
 * For a pooling connection with Tomcat, the META-INF/context.xml file should look something
 * like the following:
<?xml version="1.0" encoding="UTF-8"?>
<Context antiJARLocking="true" path="/Sancho_Task">
    <!-- maxActive: Maximum number of dB connections in pool. Make sure you
         configure your mysqld max_connections large enough to handle
         all of your db connections. Set to -1 for no limit.
         -->

    <!-- maxIdle: Maximum number of idle dB connections to retain in pool.
         Set to -1 for no limit.  See also the DBCP documentation on this
         and the minEvictableIdleTimeMillis configuration parameter.
         -->

    <!-- maxWait: Maximum time to wait for a dB connection to become available
         in ms, in this example 10 seconds. An Exception is thrown if
         this timeout is exceeded.  Set to -1 to wait indefinitely.
         -->

    <!-- username and password: MySQL dB username and password for dB connections  -->

    <!-- driverClassName: Class name for the old mm.mysql JDBC driver is
         org.gjt.mm.mysql.Driver - we recommend using Connector/J though.
         Class name for the official MySQL Connector/J driver is com.mysql.jdbc.Driver.
         -->

    <!-- url: The JDBC connection url for connecting to your MySQL dB.
         -->
	[...rest of Context definition, if any]
	
  <Resource name="jdbc/SanchoDB" auth="Container" type="javax.sql.DataSource"
               maxActive="10"
               maxIdle="5"
               maxWait="10000"
               removeAbandoned="true"
               removeAbandonedTimeout="120"
               logAbandoned="true"
               username="sancho_user"
               password="se3cret"
               driverClassName="com.mysql.jdbc.Driver"
               url="jdbc:mysql://localhost:3306/sancho?autoReconnect=true"/>
</Context>

 * OR the WEB-INF file should contain a section similar to the following:
     <resource-ref>
        <description>Sancho DB Connection</description>
        <res-ref-name>jdbc/SanchoDB</res-ref-name>
        <res-type>javax.sql.DataSource</res-type>
        <res-auth>Container</res-auth>
    </resource-ref>
    
From the Tomcat 5.5 javadoc: "If a resource has been defined in a <Context> element it is 
not necessary for that resource to be defined in /WEB-INF/web.xml. However, it is recommended 
to keep the entry in /WEB-INF/web.xml to document the resource requirements for the web 
application."

 *
 * Note that DbManager is not I18N -- we feel that the messages and logging can be left in English as they
 * (hopefully) will not be viewed by end users.
 * 
 * @author hulles
 */
final public class DbManager {
    private final static Level LOGLEVEL = Level.FINER;
    private final static String KEYSTMT = "SELECT * FROM entity WHERE `key` = '%s'";
    private final static String UPDATEKEYSTMT = "UPDATE entity SET `id_value` = %d WHERE `key` = '%s'";
    private static DbManager instance;
    private Connection saveConn;
    private static SanchoApplication app = null;

    /**
     * Create the DBManager class. Do not create a new instance, call
     * DBManager.getInstance() instead.
     */
    private DbManager() {
        Connection conn = null;

        try {
	        if (app == null) {
	        	throw new NoApplicationException();
	        }
        } catch (NoApplicationException ex) {
        	Utils.error("You must set the application in DbManager prior to instantiating it");
        	ex.printStackTrace();
        }
        Utils.log(LOGLEVEL, "DbManager: getting connection");
        try {
            conn = newDatabaseConnection();
        } catch (NoConnectionException ex) {
            Utils.error("Unable to create database connection");
            ex.printStackTrace();
        }
        if (app.useSingleConnection()) {
            saveConn = conn;
        } else {
            saveConn = null;
        }
        closeConnection(conn);
    }

    /**
     * Get the SanchoApplication that DbManager is currently using
     * @return The SanchoApplication
     */
    public static SanchoApplication getApplication() {
    	return app;
    }
    
    /**
     * Set the SanchoApplication for DbManager to use
     * @param myApp The SanchoApplication to use
     */
    public static void setApplication(SanchoApplication myApp) {
    	app = myApp;
    }
    
    /**
     * Get an instance of DBManager. Use this instead of creating a new instance
     * yourself.
     * @return The instance of DBManager.
     */
    public static synchronized DbManager getInstance() {
        if (instance == null) {
            instance = new DbManager();
        }
        return instance;
    }

    /*** DATABASE METHODS ***/
    /**
     * Get a MySQL database connection. This connection is reused; for a new one
     * call newDatabaseConnection() instead.
     * @return Reference to the default MySQL connection.
     * @throws com.hulles.sancho.engine.DbManager.NoConnectionException
     */
    public synchronized Connection getConnection() throws NoConnectionException {
        if (app.useSingleConnection()) {
            return getSingleConnection();
        } else {
            return newDatabaseConnection();
        }
    }

    private synchronized Connection getSingleConnection() throws NoConnectionException {
        boolean valid;
        String msg;

        if (LOGLEVEL == Level.FINEST) {
            valid = false;
            if (saveConn != null) {
                // test for a valid connection; adds overhead so be careful
                try {
                    valid = saveConn.isValid(10);
                } catch (SQLException ex) {
                    Utils.sqlError(ex);
                }
            }
            // returns "normal" saved connection
            msg = "DbManager: returning connection, valid = " + valid;
            Utils.log(LOGLEVEL, msg);
        }
        if (saveConn == null) {
            throw new NoConnectionException();
        }
        return saveConn;
    }

    /**
     * Get a new MySQL database connection. Unless you specifically need a newly-created
     * connection you should use getDatabaseConnection() instead.
     * @return The MySQL connection
     */
    private synchronized Connection newDatabaseConnection() throws NoConnectionException {
        DataSource ds;
        Connection myConn;

        try {
            ds = app.getDataSource();
        } catch (NoDataSourceException ex) {
            Utils.error("Unable to get data source");
            throw new NoConnectionException();
        }
        assert ds != null : "System error: null data source but no exception thrown";
//        Utils.log(LOGLEVEL, "New Connection: server name: " + app.getServerName());
//        Utils.log(LOGLEVEL, "New Connection: database name: " + app.getDatabaseName());
//        Utils.log(LOGLEVEL, "New Connection: user name: " + app.getUserName());
//        Utils.log(LOGLEVEL, "New Connection: password: " + app.getPassword());
        Utils.log(LOGLEVEL, "New Connection: database source: " + ds.toString());
        try {
            myConn = ds.getConnection();
            if (myConn == null) {
                Utils.error("Unable to create connection");
                throw new NoConnectionException();
            }
        } catch (SQLException ex) {
            Utils.error("Unable to create connection", ex.getMessage());
            Utils.sqlError(ex);
            throw new NoConnectionException();
        }
        return myConn;
    }

    /**
     * Closes the MySQL connection.
     * @param myConn The MySQL connection to be closed.
     */
    public synchronized void closeConnection(Connection myConn) {
        if (myConn == null) {
            return;
        }
        if (app.useSingleConnection()) {
            if (myConn == saveConn) {
                // don't close the single connection!
                return;
            }
        }
        try {
            Utils.log(LOGLEVEL, "DbManager: closing connection");
            myConn.close();
        } catch (SQLException ex) {
            Utils.sqlError(ex);
        } finally {
            myConn = null;
        }
    }

    /**
     * Execute a database query, using default cursor type and result type.
     * @param query The SQL statement to execute
     * @return The result set
     */
    public synchronized ResultSet executeQuery(String query) {
        return executeQuery(query, ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);
    }

    /**
     * Execute a database query, using specified cursor type and result type.
     * @param query The SQL statement to execute
     * @param resultSetType ResultSet.TYPE_FORWARD_ONLY, e.g.
     * @param resultSetConcurrency ResultSet.CONCUR_READ_ONLY, e.g.
     * @return The result set
     */
    public synchronized ResultSet executeQuery(String query, int resultSetType, int resultSetConcurrency) {
        Connection conn;
        Statement stmt = null;
        ResultSet result = null;

        if (query == null) {
            Utils.error("Null query in executeQuery");
            return null;
        }
        try {
            conn = getConnection();
        } catch (NoConnectionException ex) {
            Utils.error("Unable to create connection");
            return null;
        }
        try {
            stmt = conn.createStatement(resultSetType, resultSetConcurrency);
            result = stmt.executeQuery(query);
        } catch (SQLException ex) {
            Utils.sqlError(ex);
            return null;
        }
        return result;
    }



    /**
     * Execute an update against the database
     * @param query The SQL statement to execute
     */
    public synchronized int executeUpdate(String query) {
        Connection conn;
        int rows;

        if (query == null) {
            Utils.error("Null query in executeUpdate");
            return 0;
        }
        try {
            conn = getConnection();
        } catch (NoConnectionException ex) {
            Utils.error("Unable to create connection");
            return 0;
        }
        rows = executeUpdate(query, conn);
        closeConnection(conn);
        return rows;
    }
    
    /**
     * Execute an update against the database, passing a connection. This should be
     * used for transactions, when called with two parameters.
     * @param query The SQL statement to execute
     * @param conn The connection to use for the update
     */
    public synchronized int executeUpdate(String query, Connection conn) {
        Statement stmt = null;
        int rows = 0;

        if (query == null) {
            Utils.error("Null query in executeUpdate");
            return 0;
        }
        try {
            stmt = conn.createStatement();
            rows = stmt.executeUpdate(query);
        } catch (SQLException ex) {
            Utils.sqlError(ex);
            return 0;
        } finally {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException ex) {}
                stmt = null;
            }
            // leave connection open!
        }
        return rows;
    }

    /**
     * Get a "field" from the database, i.e. retrieve one value from an entity
     * table and return it as a String.
     * @param query The SQL query to execute.
     * @return The value as a String, or null
     */
    public synchronized String getStringField(String query) {
        ResultSet result;
        String fieldVal = null;

        result = executeQuery(query);
        if (result != null) {
            try {
                if (result.next()) {
                    fieldVal = result.getString(1);
                } else {
                    Utils.log(Level.FINER, "Field value not found: ", query);
                    fieldVal = null;
                }
            } catch(SQLException ex) {
                Utils.sqlError(ex);
            } finally {
                closeResult(result);
            }
        }
        return fieldVal;
    }

    /**
     * Get a "field" from the database, i.e. retrieve one value from an entity
     * table and return it as a Double.
     * @param query The SQL query to execute.
     * @return The value as a Double, or null
     */
    public synchronized Double getDoubleField(String query) {
        ResultSet result;
        Double fieldVal = null;

        result = executeQuery(query);
        if (result != null) {
            try {
                if (result.next()) {
                    fieldVal = result.getDouble(1);
                } else {
                    Utils.log(Level.FINER, "Field value not found: ", query);
                    fieldVal = null;
                }
            } catch(SQLException ex) {
                Utils.sqlError(ex);
            } finally {
                closeResult(result);
            }
        }
        return fieldVal;
    }

    /**
     * Get a "field" from the database, i.e. retrieve one value from an entity
     * table and return it as a Float.
     * @param query The SQL query to execute.
     * @return The value as a Float, or null
     */
    public synchronized Float getFloatField(String query) {
        ResultSet result;
        Float fieldVal = null;

        result = executeQuery(query);
        if (result != null) {
            try {
                if (result.next()) {
                    fieldVal = result.getFloat(1);
                } else {
                    Utils.log(Level.FINER, "Field value not found: ", query);
                    fieldVal = null;
                }
            } catch(SQLException ex) {
                Utils.sqlError(ex);
            } finally {
                closeResult(result);
            }
        }
        return fieldVal;
    }

    /**
     * Get a "field" from the database, i.e. retrieve one value from an entity
     * table and return it as a java.sql.Data.
     * @param query The SQL query to execute.
     * @return The value as a Date, or null
     */
    public synchronized Date getDateField(String query) {
        ResultSet result;
        Date fieldVal = null;

        result = executeQuery(query);
        if (result != null) {
            try {
                if (result.next()) {
                    fieldVal = result.getDate(1);
                } else {
                    Utils.log(Level.FINER, "Field value not found: ", query);
                    fieldVal = null;
                }
            } catch(SQLException ex) {
                Utils.sqlError(ex);
            } finally {
                closeResult(result);
            }
        }
        return fieldVal;
    }

    /**
     * Get a "field" from the database, i.e. retrieve one value from an entity
     * table and return it as a java.sql.Time.
     * @param query The SQL query to execute.
     * @return The value as a Time, or null
     */
    public synchronized Time getTimeField(String query) {
        ResultSet result;
        Time fieldVal = null;

        result = executeQuery(query);
        if (result != null) {
            try {
                if (result.next()) {
                    fieldVal = result.getTime(1);
                } else {
                    Utils.log(Level.FINER, "Field value not found: ", query);
                    fieldVal = null;
                }
            } catch(SQLException ex) {
                Utils.sqlError(ex);
            } finally {
                closeResult(result);
            }
        }
        return fieldVal;
    }

    /**
     * Get a "field" from the database, i.e. retrieve one value from an entity
     * table and return it as a java.sql.Timestamp.
     * @param query The SQL query to execute.
     * @return The value as a Timestamp, or null
     */
    public synchronized Timestamp getTimestampField(String query) {
        ResultSet result;
        Timestamp fieldVal = null;

        result = executeQuery(query);
        if (result != null) {
            try {
                if (result.next()) {
                    fieldVal = result.getTimestamp(1);
                } else {
                    Utils.log(Level.FINER, "Field value not found: ", query);
                    fieldVal = null;
                }
            } catch(SQLException ex) {
                Utils.sqlError(ex);
            } finally {
                closeResult(result);
            }
        }
        return fieldVal;
    }

    /**
     * Get a "field" from the database, i.e. retrieve one value from an entity
     * table and return it as a byte array.
     * @param query The SQL query to execute.
     * @return The value as a byte array, or null
     */
    public synchronized byte[] getBytesField(String query) {
        ResultSet result;
        byte[] bytes = null;

        result = executeQuery(query);
        if (result != null) {
            try {
                if (result.next()) {
                    bytes = result.getBytes(1);
                } else {
                    Utils.log(Level.FINER, "Field value not found: ", query);
                    bytes = null;
                }
            } catch(SQLException ex) {
                Utils.sqlError(ex);
            } finally {
                closeResult(result);
            }
        }
        return bytes;
    }

    /**
     * Get a "field" from the database, i.e. retrieve one value from an entity
     * table and return it as an Integer. Note that MySQL Connector/J returns the
     * equivalent of a java.lang.Long if the database field is INTEGER(UNSIGNED),
     * therefore this may truncate in the case of very large values.
     * @param query The SQL query to execute.
     * @return The value as an Integer, or null
     */
    public synchronized Integer getIntegerField(String query) {
        ResultSet result;
        Integer fieldVal = null;

        result = executeQuery(query);
        if (result != null) {
            try {
                if (result.next()) {
                    fieldVal = result.getInt(1);
                } else {
                    Utils.log(Level.FINER, "Field value not found: ", query);
                    fieldVal = null;
                }
            } catch(SQLException ex) {
                Utils.sqlError(ex);
            } finally {
                closeResult(result);
            }
        }
        return fieldVal;
    }

    /**
     * Get a "field" from the database, i.e. retrieve one value from an entity
     * table and return it as a Long.
     * @param query The SQL query to execute.
     * @return The value as a Long, or null
     */
    public synchronized Long getLongField(String query) {
        ResultSet result;
        Long fieldVal = null;

        result = executeQuery(query);
        if (result != null) {
            try {
                if (result.next()) {
                    fieldVal = result.getLong(1);
                } else {
                    Utils.log(Level.FINER, "Field value not found: ", query);
                    fieldVal = null;
                }
            } catch(SQLException ex) {
                Utils.sqlError(ex);
            } finally {
                closeResult(result);
            }
        }
        return fieldVal;
    }

    /**
     * Get a "field" from the database, i.e. retrieve one value from an entity
     * table and return it as an object.
     * @param query The SQL query to execute.
     * @return The value as an Object, or null
     */
    public synchronized Boolean getBooleanField(String query) {
        ResultSet result;
        Boolean fieldVal = null;

        result = executeQuery(query);
        if (result != null) {
            try {
                if (result.next()) {
                    fieldVal = result.getBoolean(1);
                } else {
                    Utils.log(Level.FINER, "Field value not found: ", query);
                    fieldVal = null;
                }
            } catch(SQLException ex) {
                Utils.sqlError(ex);
            } finally {
                closeResult(result);
            }
        }
        return fieldVal;
    }

    /**
     * Close the result set properly
     * @param result The result set to close
     */
    public synchronized void closeResult(ResultSet result) {
        Statement stmt = null;
        Connection conn = null;

        if (result == null) {
            return;
        }
        try {
            stmt = result.getStatement();
            if (stmt != null) {
                conn = stmt.getConnection();
            }
        } catch (SQLException ex) {}
        try {
            result.close();
        } catch (SQLException ex) {}
        result = null;
        if (stmt != null) {
            try {
                stmt.close();
            } catch (SQLException ex) {}
            stmt = null;
        }
        closeConnection(conn);
    }

    /**
     * Create a new MySQL connection and begin a transaction with it.
     * @return The new MySQL connection with the transaction begun.
     * @throws com.hulles.sancho.engine.DbManager.NoConnectionException
     */
    public synchronized Connection startTransaction()  throws NoConnectionException {
        Connection tranConn;

        tranConn = newDatabaseConnection();
        if (tranConn != null) {
            try {
                Utils.log(LOGLEVEL, "DbManager: setting transaction connection");
                tranConn.setTransactionIsolation(Connection.TRANSACTION_READ_UNCOMMITTED);
                tranConn.setAutoCommit(false);
            } catch (SQLException ex) {
                Utils.sqlError(ex);
            }
        }
        Utils.log(LOGLEVEL, "DbManager: start transaction");
        return tranConn;
    }

    /**
     * Commit the current MySQL transaction.
     * @param tranConn The connection with the started transaction to commit.
     */
    public synchronized void commitTransaction(Connection tranConn) {
        assert tranConn != null : "Connection cannot be null";
        try {
            tranConn.commit();
            Utils.log(LOGLEVEL, "DbManager: commit transaction");
        } catch (SQLException ex) {
            Utils.sqlError(ex);
        }
    }

    /**
     * Rollback the current MySQL transaction.
     * @param tranConn The connection with the started transaction to roll back.
     */
    public synchronized void rollbackTransaction(Connection tranConn) {
        assert tranConn != null : "Connection cannot be null";
        try {
            tranConn.rollback();
            Utils.log(LOGLEVEL, "DbManager: rollback transaction");
        } catch (SQLException ex) {
            Utils.sqlError(ex);
        }
    }

    @Override
    protected void finalize() {
        try {
            super.finalize();
        } catch (Throwable ex) {
            Utils.error("Error in finalizing DbManager (super)", ex);
            return;
        }
        Utils.log(LOGLEVEL, "DbManager: finalize");
        if (saveConn != null) {
            try {
                if (!saveConn.isClosed()) {
                    // isClosed() does not query the server to see if the connection is
                    //  still valid. Use conn.execute("SELECT 1") e.g. to cause fault if
                    //  the connection is not valid (per MySQL Connector/J docs)
                    saveConn.close();
                }
            } catch (SQLException ex) {
                Utils.sqlError(ex);
            }
            saveConn = null;
        }
        instance = null;
    }

    /**
     * Get a new MySQL ID value.
     * @param key The key that matches the record in the entity table.
     * @return The next ID value in sequence (incremented by 1 each time).
     */
    public synchronized Integer getNewID(String key) {
        ResultSet result;
        Integer id_value = null;
        String keyString;

        Utils.log(LOGLEVEL, "DbManager: getNewID");
        keyString = String.format(KEYSTMT, key);
        result = executeQuery(keyString, ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
        if (result == null) {
            Utils.error("Unable to get key statement");
            return null;
        }
        try {
            if (result.next()) {
                id_value = result.getInt("id_value");
                id_value += 1;
                result.updateInt("id_value", id_value);
                result.updateRow();
            } else {
                Utils.error("Unable to find id_generator key value : " + key);
            }
        } catch (SQLException ex) {
            Utils.sqlError(ex);
        } finally {
            closeResult(result);
        }
        return id_value;
    }

    /**
     * Update the key ID value. Use this to set an initial value for the ID.
     * @param key The key that matches the record in the id_generator table.
     * @param value The value with which to update the ID
     */
    public synchronized void updateID(String key, Integer value) {
        Statement stmt = null;
        Connection conn;

        Utils.log(LOGLEVEL, "DbManager: updateID");
        try {
            conn = getConnection();
        } catch (NoConnectionException ex) {
            Utils.error("Unable to get connection");
            return;
        }
        try {
            stmt = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE);
            stmt.executeUpdate(String.format(UPDATEKEYSTMT, value, key));
            stmt.close();
        } catch (SQLException ex) {
            Utils.sqlError(ex);
        } finally{
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException ex) {} // ignore
            }
            stmt = null;
            closeConnection(conn);
        }
    }

    /**
     * Get a MySQL date stamp for the current time.
     * @return A string representation of the date stamp.
     */
    public String getSQLDateStamp() {
        ResultSet result;
        Statement stmt = null;
        Connection conn;
        Timestamp timeStamp = null;

        // '2007-12-15 23:50:26'  e.g. (21 chars)
        Utils.log(LOGLEVEL, "DbManager: getSQLDateStamp");
        try {
            conn = getConnection();
        } catch (NoConnectionException ex) {
            Utils.error("Unable to get connection");
            return null;
        }
        try {
            stmt = conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_READ_ONLY);
            result = stmt.executeQuery("SELECT NOW()");
            if (result.next()) {
                timeStamp = result.getTimestamp(1);
            } else {
                timeStamp = null;
            }
        } catch (SQLException ex) {
            Utils.sqlError(ex);
        } finally{
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException ex) {} // ignore
            }
            stmt = null;
            closeConnection(conn);
        }
        return (timeStamp == null ? null : timeStamp.toString());
    }

    /**
     * This exception serves to remind client apps to deal with the case
     * where there is no database connection available
     */
    public static class NoConnectionException extends Exception {
		private static final long serialVersionUID = 7253139850492821633L;

		/**
         * A simple exception to be thrown if no database connection is available
         */
        public NoConnectionException() {
            super();
        }
    }

    /**
     * This exception serves to remind client apps to set the SanchoApplication
     * by calling the static setApplication method prior to instantiating DbManager
     */
    public static class NoApplicationException extends Exception {
		private static final long serialVersionUID = -5407804599019345488L;

		/**
         * A simple exception to be thrown if no database connection is available
         */
        public NoApplicationException() {
            super();
        }
    }

}
